﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace GSManager
{
    public class DoanhThu
    {
        private int _STT;
        private decimal _TongTienBan;
        private decimal _TongTienDV;
        private decimal _TongTienMua;
        private decimal _TongTienChi;
        private decimal _TongTienTraGC;
        private decimal _TongTienNo;
        public int STT
        {
            get { return _STT; }
            set { _STT = value; }
        }
        public decimal TongTienBan
        {
            get { return _TongTienBan; }
            set { _TongTienBan = value; }
        }
        public decimal TongTienDV
        {
            get { return _TongTienDV; }
            set { _TongTienDV = value; }
        }
        public decimal TongTienMua
        {
            get { return _TongTienMua; }
            set { _TongTienMua = value; }
        }
        public decimal TongTienChi
        {
            get { return _TongTienChi; }
            set { _TongTienChi = value; }
        }
        public decimal TongTienTraGC
        {
            get { return _TongTienTraGC; }
            set { _TongTienTraGC = value; }
        }
        public decimal TongTienNo
        {
            get { return _TongTienNo; }
            set { _TongTienNo = value; }
        }
        public void TaoBaoCao(DateTime tungay, DateTime denngay)
        {
            OleDbConnection connect = KetNoi.KetNoiDuLieu();
            string sql = "select SUM(TongTien),SUM(TienNo) from PHIEUBANHANG where NgayBan>=? and NgayBan<=?";
            OleDbCommand cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("TuNgay", OleDbType.Date);
            cmd.Parameters[0].Value = tungay;
            cmd.Parameters.Add("DenNgay", OleDbType.Date);
            cmd.Parameters[1].Value = denngay;
            OleDbDataReader r = cmd.ExecuteReader();
            while (r.Read())
            {
                try
                {
                    TongTienBan = (decimal)r[0];
                    TongTienNo = (decimal)r[1];
                }
                catch (Exception) { }
            }
            r.Close();
             sql = "select SUM(TongTien),SUM(TienNo) from PHIEUDICHVU where NgayGiaoDich>=? and NgayGiaoDich<=?";
            cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("TuNgay", OleDbType.Date);
            cmd.Parameters[0].Value = tungay;
            cmd.Parameters.Add("DenNgay", OleDbType.Date);
            cmd.Parameters[1].Value = denngay;
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                try
                {
                    TongTienDV = (decimal)r[0];
                    TongTienNo += (decimal)r[1];
                }
                catch (Exception) { }
            }
            r.Close();
            sql = "select SUM(TongTien) from PHIEUMUAHANG where NgayMua>=? and NgayMua<=?";
            cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("TuNgay", OleDbType.Date);
            cmd.Parameters[0].Value = tungay;
            cmd.Parameters.Add("DenNgay", OleDbType.Date);
            cmd.Parameters[1].Value = denngay;
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                try
                {
                    TongTienMua = (decimal)r[0];
                }
                catch (Exception) { }
            }
            r.Close();
            sql = "select SUM(TongTienChi) from PHIEUCHI where NgayChi>=? and NgayChi<=?";
            cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("TuNgay", OleDbType.Date);
            cmd.Parameters[0].Value = tungay;
            cmd.Parameters.Add("DenNgay", OleDbType.Date);
            cmd.Parameters[1].Value = denngay;
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                try
                {
                    TongTienChi = (decimal)r[0];
                }
                catch (Exception) { }
            }
            r.Close();
            sql = "select SUM(TongTien) from PHIEUGIACONG where NgayGiaCong>=? and NgayGiaCong<=?";
            cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("TuNgay", OleDbType.Date);
            cmd.Parameters[0].Value = tungay;
            cmd.Parameters.Add("DenNgay", OleDbType.Date);
            cmd.Parameters[1].Value = denngay;
            r = cmd.ExecuteReader();
            while (r.Read())
            {
                try
                {
                    TongTienTraGC = (decimal)r[0];
                }
                catch (Exception) { }
            }
            r.Close();
            sql = "select Max(STT) from DOANHTHU";
            cmd = new OleDbCommand(sql, connect);
            r = cmd.ExecuteReader();
            while (r.Read())
            {
               try
                {
                    STT = (int)r[0];
                    STT++;
                }
                catch (Exception) { STT = 1; }
            }
            r.Close();
            sql = "insert into DOANHTHU values(?,?,?,?,?,?,?,?,?)";
            cmd = new OleDbCommand(sql, connect);
            cmd.Parameters.Add("@STT", OleDbType.Integer);
            cmd.Parameters.Add("@TongTienBan", OleDbType.Decimal);
            cmd.Parameters.Add("@TongTienDV", OleDbType.Decimal);
            cmd.Parameters.Add("@TongTienMua", OleDbType.Decimal);
            cmd.Parameters.Add("@TongTienChi", OleDbType.Decimal);
            cmd.Parameters.Add("@TongTienGC", OleDbType.Decimal);
            cmd.Parameters.Add("@TongTienNo", OleDbType.Decimal);
            cmd.Parameters.Add("@TuNgay", OleDbType.Date);
            cmd.Parameters.Add("@DenNgay", OleDbType.Date);
            cmd.Parameters[0].Value = STT;
            cmd.Parameters[1].Value = TongTienBan;
            cmd.Parameters[2].Value = TongTienDV;
            cmd.Parameters[3].Value = TongTienMua;
            cmd.Parameters[4].Value = TongTienChi;
            cmd.Parameters[5].Value = TongTienTraGC;
            cmd.Parameters[6].Value = TongTienNo;
            cmd.Parameters[7].Value = tungay;
            cmd.Parameters[8].Value = denngay;
            cmd.ExecuteNonQuery();
            connect.Close();
        }
    }
}
